﻿Option Explicit On

Imports System.Text
Imports System.Data
Imports CRM.BusinessLayer
Imports Telerik.Windows.Controls
Imports Telerik.Windows.Data
Imports System.ComponentModel
Partial Public Class frmSearch
    Dim startDate As DateTime = CommonDataFunctions.GetLocalServer
    Private m_bDoubleFire As Boolean = False
    Dim StrFieldNames() As String
    Private WithEvents GBWorker As System.ComponentModel.BackgroundWorker
    Public blnLoadMainform As Boolean = False
    Private Enum SearchModes
        ClientFirstName = 1
        ClientLastName = 2
        CompanyName = 3
        DOB = 4
        ExRef = 5
        Email = 6
        Phone = 7
    End Enum

    Private Enum NationalityMode
        All = 1
        NonUK = 2
        UKOnly = 3
        Specific = 4
    End Enum

    Private Enum CaseStatusMode
        AllClients = 1
        ActiveLead = 2
        AnyOpen = 3
        Initiated = 4
    End Enum

    Private Sub frmSearch_KeyUp(ByVal sender As Object, ByVal e As System.Windows.Input.KeyEventArgs) Handles Me.KeyUp
        If e.Key = Key.Escape Then
            blnLoadMainform = False
            Me.Close()
        End If
    End Sub

    Private Sub frmSearch_Loaded(ByVal sender As Object, ByVal e As System.Windows.RoutedEventArgs) Handles Me.Loaded
        GeneralTasks.ChangeTheme(Me)
        GeneralTasks.EnumerateandFill(Me.LogicalChildren)
        With Me.drpNationality
            .DisplayMemberPath = "country_nationality"
            .SelectedValuePath = "country_id"
        End With
        DataBinding.BindComboBox(CRM.BusinessLayer.CommonDataFunctions.NationalityList(), Me.drpNationality)
        Me.drpNationality.Visibility = Windows.Visibility.Hidden
        Me.txtSearchCriteria.Focus()
    End Sub

    Private Sub SearchType(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles radClientFName.Checked, radClientLName.Checked, radCompanyName.Checked, radDOB.Checked, radEmail.Checked, radPhoneNumber.Checked, radExtRef.Checked
        If Not CType(sender, RadioButton).Content Is Nothing Then
            If CType(sender, RadioButton).Content.Trim = "DOB" Then
                Me.lblSearchCriteria.Content = CType(sender, RadioButton).Content & " (dd-mmm-yy)"
                Me.chkExactMatch.IsChecked = False
                Me.chkExactMatch.IsEnabled = False
                Me.chkExactMatch.Content = String.Empty
            Else
                Dim radName As String = CType(sender, RadioButton).Name
                If radName = "radCompanyName" Or radName = "radExtRef" Then
                    radClientFName.IsChecked = False
                    radClientLName.IsChecked = False
                    radEmail.IsChecked = False
                    radPhoneNumber.IsChecked = False
                    radDOB.IsChecked = False
                End If
                If radName = "radClientFName" Or radName = "radClientLName" Or radName = "radDOB" Or radName = "radPhoneNumber" Or radName = "radEmail" Then
                    radCompanyName.IsChecked = False
                    radExtRef.IsChecked = False
                End If
                Me.chkExactMatch.IsEnabled = True
                Me.chkExactMatch.Content = "Exact Match"
                Me.lblSearchCriteria.Content = CType(sender, RadioButton).Content
            End If
        End If

        If Not Me.txtSearchCriteria Is Nothing Then
            'Me.txtSearchCriteria.Text = String.Empty
            Me.txtSearchCriteria.Focus()
        End If
        If Not grdSearchResults Is Nothing Then
            grdSearchResults.ItemsSource = Nothing
        End If
    End Sub

    Private Sub btnClose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClose.Click
        blnLoadMainform = False
        Me.Close()
    End Sub

    Private Sub NationalityModeChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles radNatAll.Checked, radNatNonUK.Checked, radNatSelect.Checked, radNatUKOnly.Checked
        If Not drpNationality Is Nothing Then
            Select Case CType(CType(sender, RadioButton).Tag, NationalityMode)

                Case NationalityMode.Specific
                    Me.drpNationality.Visibility = Windows.Visibility.Visible

                Case Else
                    Me.drpNationality.Visibility = Windows.Visibility.Hidden

            End Select
        End If
        If Not txtSearchCriteria Is Nothing Then
            txtSearchCriteria.Focus()



        End If

    End Sub

    Private Sub AddUserSearch(ByVal strSearchText As String, ByVal intUserID As Integer, ByVal intSearchCount As Integer, ByVal intSearchMode As Integer)
        ' log the user's search in the D_UserSearch table (this stores the last 20 searches for each user)

        Dim strSQL As New StringBuilder
        Dim intSearchType As Integer

        ' delete 20th search
        strSQL.Append("Delete from D_UserSearch where Search_UserID = " & intUserID & " AND Search_Place > 20")
        If CommonDataFunctions.ExecuteSQL(strSQL.ToString) = False Then
            Exit Sub
        End If

        ' increment the search numbers up by one
        strSQL.Remove(0, strSQL.Length)
        strSQL.Append("Update D_UserSearch set Search_Place = Search_Place + 1 where Search_UserID = " & intUserID)
        If CommonDataFunctions.ExecuteSQL(strSQL.ToString) = False Then
            Exit Sub
        End If

        ' log current search
        strSQL.Remove(0, strSQL.Length)
        strSQL.Append("Update D_UserSearch set Search_Place = Search_Place + 1 where Search_UserID = " & intUserID)
        If CommonDataFunctions.ExecuteSQL(strSQL.ToString) = False Then
            Exit Sub
        End If

        ' convert the SearchMode to the SearchType as they don't quite match up
        Select Case intSearchMode
            Case 1
                intSearchType = 1
            Case 2
                intSearchType = 1
            Case 3
                intSearchType = 4
            Case 4
                intSearchType = 0 ' I don't think this type of search is recorded?
            Case 5
                intSearchType = 2
        End Select

        ' company name search value value gets stored in a different column (for some bizarre reason)
        If intSearchMode = 2 Then
            strSQL.Append("Insert into D_UserSearch (Search_CompanyName, Search_UserID, Search_Count, Search_Type) ")
        Else
            strSQL.Append("Insert into D_UserSearch (Search_Surname, Search_UserID, Search_Count, Search_Type) ")
        End If
        strSQL.Append("VALUES ('" & strSearchText & "', " & intUserID & "," & intSearchCount & "," & intSearchType & ")")

        If CommonDataFunctions.ExecuteSQL(strSQL.ToString) = False Then
            Exit Sub
        End If

    End Sub

    Private Sub grdSearchResults_MouseDoubleClick(ByVal sender As Object, ByVal e As System.Windows.Input.MouseButtonEventArgs) Handles grdSearchResults.MouseDoubleClick

        Dim currentRow As DataRow = grdSearchResults.CurrentItem

        If Me.grdSearchResults.SelectedItems.Count > 0 Then
            GlobalCRM.ResetGlobalVars()
            Dim commonFunc As New CommonFuncs()
            If IsNumeric(currentRow(0)) Then
                GlobalCRM.CurrentCandidateID = currentRow(0)
                CommonDataFunctions.GblFunc_Goto_ID(GlobalCRM.CurrentCandidateID, CommonFuncs.DBIdTypes.PersonID)
            ElseIf IsNumeric(currentRow(4)) Then
                GlobalCRM.CurrentCompanyID = currentRow(4)
                CommonDataFunctions.GblFunc_Goto_ID(GlobalCRM.CurrentCompanyID, CommonFuncs.DBIdTypes.CompanyID)
            End If
            'CommonFuncs.CloseMDIForm("frmMainDisplay")
            'frmMainDisplay.MdiParent = frmMDIMain
            'Dim mainDisplayForm As New frmMainDisplay()
            'mainDisplayForm.Show()
            frmCRMMainDisplay.loadTabCompany = False
            frmCRMMainDisplay.loadTabCases = False
            frmCRMMainDisplay.loadTabAccounts = False
            frmCRMMainDisplay.loadTabDocs = False
            frmCRMMainDisplay.loadTabPeople = False
            frmCRMMainDisplay.loadTabEvent = False
            blnLoadMainform = True
            Me.Close()
        End If


        'If currentRowIndex = -1 Then
        '    MsgBox("No record seletced ! Please search the records first", MsgBoxStyle.Information)
        '    Exit Sub
        'Else
        '    If Me.grdSearchResults.SelectedItems.Count > 0 Then

        '        'GlobalCRM.ResetGlobalVars()
        '        'Dim commonFunc As New CommonFuncs()
        '        'If IsNumeric(Me.grdSearchResults.SelectedItems(0).SubItems(0).Text) Then
        '        '    GlobalCRM.CurrentCandidateID = Me.grdSearchResults.SelectedItems(0).SubItems(0).Text
        '        '    commonFunc.GblFunc_Goto_ID(GlobalCRM.CurrentCandidateID, commonFunc.DBIdTypes.PersonID)
        '        'ElseIf IsNumeric(Me.grdSearchResults.SelectedItems(0).SubItems(4).Text) Then
        '        '    GlobalCRM.CurrentCompanyID = Me.grdSearchResults.SelectedItems(0).SubItems(4).Text
        '        '    CommonFunc.GblFunc_Goto_ID(GlobalCRM.CurrentCompanyID, CommonFunc.DBIdTypes.CompanyID)
        '        'End If
        '        'CommonFunc.CloseMDIForm("frmMainDisplay")
        '        'frmMainDisplay.MdiParent = frmMDIMain
        '        'frmMainDisplay.Show()
        '        'Me.Dispose()
        '    End If
        'End If
    End Sub

    Private Sub lvwCases_MouseDoubleClick(ByVal sender As Object, ByVal e As System.Windows.Input.MouseButtonEventArgs) Handles lvwCases.MouseDoubleClick
        '' go to selected case

        If Me.lvwCases.SelectedItems.Count = 0 Then
            Exit Sub
        End If
        Dim selectedRow As DataRowView = Me.lvwCases.SelectedItem
        GlobalCRM.CurrentCaseID = selectedRow(0)
        CommonDataFunctions.GblFunc_Goto_ID(GlobalCRM.CurrentCaseID, CommonFuncs.DBIdTypes.CaseID)
        blnLoadMainform = True
        Me.Close()
        'GlobalCRM.CurrentSearchType = GlobalCRM.enuSearchType.eCase

        'frmMainDisplay.MdiParent = frmMDIMain
        'frmMainDisplay.Show()

        'Me.Dispose()
    End Sub

    Private Sub btnHistory_Click(ByVal sender As System.Object, ByVal e As System.Windows.RoutedEventArgs) Handles btnHistory.Click

        Dim historyForm As New frmSearchHistory
        historyForm.Show()
        blnLoadMainform = False
        Me.Close()
        'Me.btnSearch_Click(Me, Nothing)
    End Sub

    Private Sub grdSearchResults_SelectionChanged(ByVal sender As Object, ByVal e As Telerik.Windows.Controls.SelectionChangeEventArgs) Handles grdSearchResults.SelectionChanged
        'Dim currentRowIndex As Integer = grdSearchResults.SelectedIndex
        'Dim currentRowIndex = grdSearchResults.CurrentItem
        'If currentRowIndex = -1 Then
        '    MsgBox("Please search the records from the searched items first !", MsgBoxStyle.Information)
        '    Exit Sub
        'Else
        Dim currentRow As DataRow = grdSearchResults.CurrentItem

        If Me.grdSearchResults.SelectedItems.Count > 0 Then

            Dim strPersonID As String = currentRow(0).ToString()
            Dim strCompanyID As String = currentRow(4).ToString()

            If strPersonID = String.Empty Then
                ' get cases for company
                DataBinding.BindListView(Company.CompanyCases(CInt(strCompanyID)), Me.lvwCases)
            Else
                ' get cases for person
                DataBinding.BindListView(People.GetPersonCases(CInt(strPersonID)), Me.lvwCases)
            End If

        End If

        'End If

        ' show cases for selected person or company

    End Sub

    'Private Sub timer1_Tick(ByVal sender As Object, ByVal e As EventArgs)
    '    Dim ts As TimeSpan = DateTime.Now.Subtract(startDate)
    '    Dim sTime As String = " …" & ts.Minutes.ToString & ":" & ts.Seconds.ToString + ":" + ts.Milliseconds.ToString
    '    toolStripStatusTime.Text = sTime
    '    If toolStripProgressBar1.Value = toolStripProgressBar1.Maximum Then
    '        toolStripProgressBar1.Value = 0
    '    End If
    '    toolStripProgressBar1.PerformStep()
    'End Sub

    Private Sub GBWorker_DoWork(ByVal sender As Object, ByVal e As DoWorkEventArgs)
        ''Me.Cursor = Cursors.Wait


        ' xxx you can remove this if you are going to use the calendar popup instead of calendar on the form
        'Select Case mySearchMode
        '    Case SearchModes.DOB
        '        Me.DoSearch(Format(Me.Calendar1.MonthCalendar1.SelectionStart, "dd-MMM-yyyy"), SearchModes.DOB, myNatMode, myStatusMode, myNatId)
        '    Case Else
        '        If Me.txtSearchCriteria.Text.Trim.Length > 0 Then
        '            Me.DoSearch(Me.txtSearchCriteria.Text.Trim, mySearchMode, myNatMode, myStatusMode, myNatId)
        '        End If
        'End Select



    End Sub

    Private Sub GBWorker_RunWorkerCompleted(ByVal sender As Object, ByVal e As RunWorkerCompletedEventArgs)
        'Me.Cursor = Cursors.Arrow
        btnSearch.IsEnabled = True
        btnHistory.IsEnabled = True
    End Sub

    Private Function CurrentSearchMode() As SearchModes

        If Me.radClientFName.IsChecked Then
            Return SearchModes.ClientFirstName
        ElseIf Me.radClientLName.IsChecked Then
            Return SearchModes.ClientLastName
        ElseIf Me.radCompanyName.IsChecked Then
            Return SearchModes.CompanyName
        ElseIf Me.radDOB.IsChecked Then
            Return SearchModes.DOB
        ElseIf Me.radEmail.IsChecked Then
            Return SearchModes.Email
        ElseIf Me.radExtRef.IsChecked Then
            Return SearchModes.ExRef
        ElseIf Me.radPhoneNumber.IsChecked Then
            Return SearchModes.Phone
        End If

    End Function

    Private Function CurrentNationalityMode() As NationalityMode

        If Me.radNatAll.IsChecked Then
            Return NationalityMode.All
        ElseIf Me.radNatNonUK.IsChecked Then
            Return NationalityMode.NonUK
        ElseIf Me.radNatSelect.IsChecked Then
            Return NationalityMode.Specific
        ElseIf Me.radNatUKOnly.IsChecked Then
            Return NationalityMode.UKOnly
        End If

    End Function

    Private Function CurrentCaseMode() As CaseStatusMode

        If Me.radCaseActive.IsChecked Then
            Return CaseStatusMode.ActiveLead
        ElseIf Me.radCaseAll.IsChecked Then
            Return CaseStatusMode.AllClients
        ElseIf Me.radCaseInit.IsChecked Then
            Return CaseStatusMode.Initiated
        ElseIf Me.radCaseOpen.IsChecked Then
            Return CaseStatusMode.AnyOpen
        End If

    End Function

    Private Sub btnSearch_Click(ByVal sender As System.Object, ByVal e As System.Windows.RoutedEventArgs) Handles btnSearch.Click
        Dim searchProgress As New frmProgressBar()
        searchProgress.TextToDisplay = "Dear " & GlobalCRM.CurrentConName & ", please wait while CRM searches the records for you....."
        searchProgress.Show()
        Dim mySearchMode As SearchModes = Me.CurrentSearchMode
        Dim myNatMode As NationalityMode = Me.CurrentNationalityMode
        Dim myStatusMode As CaseStatusMode = Me.CurrentCaseMode
        Dim myNatId As Integer

        If myNatMode = NationalityMode.Specific Then
            myNatId = Me.drpNationality.SelectedValue
        End If
        'btnSearch.IsEnabled = False
        'btnHistory.IsEnabled = False
        If Len(Me.txtSearchCriteria.Text.Trim) = 0 Then
            ' - Added the message box below to prompt the user to enter the
            'search criterion
            CommonFuncs.DisplayMessage("Please enter a search criterion.", CommonDialog.CustomDialogIcons.Information, "Search Results")
            Exit Sub
        End If

        ' check user has entered at least 3 characters
        If Len(Me.txtSearchCriteria.Text.Trim) < 3 Then
            CommonFuncs.DisplayMessage("You must enter at least three characters.", CommonDialog.CustomDialogIcons.Information, "Search Results")
            Exit Sub
        End If

        ' if dob search, check is a valid date
        If radDOB.IsChecked = True And IsDate(Me.txtSearchCriteria.Text) = False Then
            CommonFuncs.DisplayMessage("This is not a valid date.", CommonDialog.CustomDialogIcons.Information, "Search Results")
            Exit Sub
        End If

        Me.DoSearch(Me.txtSearchCriteria.Text.Trim, mySearchMode, myNatMode, myStatusMode, chkExactMatch.IsChecked, myNatId)

        searchProgress.Close()

    End Sub

    Private Sub DoSearch(ByVal Criteria As String, ByVal SearchMode As SearchModes, ByVal Nationality As NationalityMode, ByVal CaseStatus As CaseStatusMode, ByVal bExactMatch As Boolean, Optional ByVal NationalityId As Integer = 0)

        Dim SQL As New StringBuilder
        'Dim SQLNat As New StringBuilder
        'Dim SQLStatus As New StringBuilder
        Dim GBWorker As New BackgroundWorker()
        AddHandler GBWorker.DoWork, AddressOf GBWorker_DoWork
        AddHandler GBWorker.RunWorkerCompleted, AddressOf GBWorker_RunWorkerCompleted
        GBWorker.RunWorkerAsync()

        ' if no search criteria entered, do nothing



        'Me.grdSearchResults.Columns(8).Text = "Email"  ' reset column name in case last search was a ExtRef search

        'Select Case Nationality
        '    Case NationalityMode.NonUK
        '        SQLNat.Append(" and d_person.person_natid <> 71 ")
        '    Case NationalityMode.UKOnly
        '        SQLNat.Append(" and d_person.person_natid = 71 ")
        '    Case NationalityMode.Specific
        '        If NationalityId > 0 Then
        '            SQLNat.Append(" and d_person.person_natid = " & NationalityId)
        '        End If
        'End Select

        'Select Case CaseStatus
        '    Case CaseStatusMode.AnyOpen
        '        SQLStatus.Append(" Inner Join (Select D_Case.Case_CandidateID from D_Case Where Case_StatusID < 1000 Group By D_Case.Case_CandidateID) Cases on D_Person.Person_ID = Cases.Case_CandidateID ")
        '    Case CaseStatusMode.ActiveLead
        '        SQLStatus.Append(" Inner Join (Select D_Case.Case_CandidateID from D_Case Where Case_StatusID < 100 Group By D_Case.Case_CandidateID) Cases on D_Person.Person_ID = Cases.Case_CandidateID ")
        '    Case CaseStatusMode.Initiated
        '        SQLStatus.Append(" Inner Join (Select D_Case.Case_CandidateID from D_Case Where Case_StatusID between 100 and 999 Group By D_Case.Case_CandidateID) Cases on D_Person.Person_ID = Cases.Case_CandidateID ")
        'End Select

        Select Case SearchMode
            Case SearchModes.ClientFirstName

                With SQL
                    .Append("Select D_Person.Person_ID as ID, D_Person.Person_Lname + ', ' + D_Person.Person_Fname as Name ,D_Person.person_DoB as DOB,")
                    .Append("Country_Nationality as Nationality , D_Company.Company_ID as CompanyID, isnull(D_Company.Company_Name,'Private') as Company,D_Company_Parent.Company_Name as ParentCompany, D_Person_Partner.Person_Lname + ', ' + D_Person_Partner.Person_Fname as Partner, EMailAddress.EMailAddress_Address as EMail FROM D_Person LEFT OUTER JOIN D_Company ON D_Person.Person_CompanyID = D_Company.Company_ID LEFT OUTER JOIN D_Company D_Company_Parent ON D_Company.Company_ParentID = D_Company_Parent.Company_ID Left Outer join L_Country on D_Person.Person_NatID = Country_ID LEFT OUTER JOIN (SELECT     D_Dependants.Dependant_ParentID, D_Person.Person_LName, D_Person.Person_FName FROM D_Person INNER JOIN D_Dependants ON D_Person.Person_ID = D_Dependants.Dependant_ChildID WHERE (D_Dependants.Dependant_Spouse = 1)) D_Person_Partner  ON D_Person.Person_ID = D_Person_Partner.Dependant_ParentID LEFT OUTER JOIN (SELECT D_EMailAddress.EMailAddress_PersonID, D_EMailAddress.EMailAddress_Address FROM D_EMailAddress  WHERE (D_EMailAddress.EMailAddress_Type = 1)) EmailAddress  ON D_Person.Person_ID = EMailAddress.EMailAddress_PersonID ")
                    'If SQLStatus.Length > 0 Then
                    '    .Append(SQLStatus.ToString)
                    'End If
                    Select Case CaseStatus
                        Case CaseStatusMode.ActiveLead
                            .Append(" Inner Join (Select D_Case.Case_CandidateID from D_Case Where Case_StatusID < 100 Group By D_Case.Case_CandidateID) Cases on D_Person.Person_ID = Cases.Case_CandidateID ")
                        Case CaseStatusMode.AnyOpen
                            .Append(" Inner Join (Select D_Case.Case_CandidateID from D_Case Where Case_StatusID < 1000 Group By D_Case.Case_CandidateID) Cases on D_Person.Person_ID = Cases.Case_CandidateID ")
                        Case CaseStatusMode.Initiated
                            .Append(" Inner Join (Select D_Case.Case_CandidateID from D_Case Where Case_StatusID between 100 and 999 Group By D_Case.Case_CandidateID) Cases on D_Person.Person_ID = Cases.Case_CandidateID ")
                    End Select
                    .Append("WHERE ")
                    If bExactMatch = True Then
                        .Append("D_Person.Person_Fname =  '" & Criteria & "' ")
                    Else
                        .Append("D_Person.Person_Fname like '%' + '" & Criteria & "' + '%' ")
                    End If
                    'If SQLNat.Length > 0 Then
                    '    .Append(SQLNat.ToString)
                    'End If

                    Select Case Nationality
                        Case NationalityMode.NonUK
                            .Append(" and d_person.person_natid <> 71 ")
                        Case NationalityMode.UKOnly
                            .Append(" and d_person.person_natid = 71 ")
                        Case NationalityMode.Specific
                            If NationalityId > 0 Then
                                .Append(" and d_person.person_natid = " & NationalityId)
                            End If
                    End Select

                    .Append(" Order By ID,D_Person.Person_Fname, D_Person.Person_Lname")
                    'DataBinding.BindListView(.ToString, Me.grdSearchResults)
                End With
            Case SearchModes.ClientLastName

                With SQL
                    .Append("Select D_Person.Person_ID as ID, D_Person.Person_Lname + ', ' + D_Person.Person_Fname as Name ,D_Person.person_DoB as DOB,")
                    .Append("Country_Nationality as Nationality , D_Company.Company_ID as CompanyID, isnull(D_Company.Company_Name,'Private') as Company,D_Company_Parent.Company_Name as ParentCompany, D_Person_Partner.Person_Lname + ', ' + D_Person_Partner.Person_Fname as Partner, EMailAddress.EMailAddress_Address as EMail FROM D_Person LEFT OUTER JOIN D_Company ON D_Person.Person_CompanyID = D_Company.Company_ID LEFT OUTER JOIN D_Company D_Company_Parent ON D_Company.Company_ParentID = D_Company_Parent.Company_ID Left Outer join L_Country on D_Person.Person_NatID = Country_ID LEFT OUTER JOIN (SELECT     D_Dependants.Dependant_ParentID, D_Person.Person_LName, D_Person.Person_FName FROM D_Person INNER JOIN D_Dependants ON D_Person.Person_ID = D_Dependants.Dependant_ChildID WHERE (D_Dependants.Dependant_Spouse = 1)) D_Person_Partner  ON D_Person.Person_ID = D_Person_Partner.Dependant_ParentID LEFT OUTER JOIN (SELECT D_EMailAddress.EMailAddress_PersonID, D_EMailAddress.EMailAddress_Address FROM D_EMailAddress  WHERE (D_EMailAddress.EMailAddress_Type = 1)) EmailAddress  ON D_Person.Person_ID = EMailAddress.EMailAddress_PersonID ")
                    'If SQLStatus.Length > 0 Then
                    '    .Append(SQLStatus.ToString)
                    'End If
                    Select Case CaseStatus
                        Case CaseStatusMode.ActiveLead
                            .Append(" Inner Join (Select D_Case.Case_CandidateID from D_Case Where Case_StatusID < 100 Group By D_Case.Case_CandidateID) Cases on D_Person.Person_ID = Cases.Case_CandidateID ")
                        Case CaseStatusMode.AnyOpen
                            .Append(" Inner Join (Select D_Case.Case_CandidateID from D_Case Where Case_StatusID < 1000 Group By D_Case.Case_CandidateID) Cases on D_Person.Person_ID = Cases.Case_CandidateID ")
                        Case CaseStatusMode.Initiated
                            .Append(" Inner Join (Select D_Case.Case_CandidateID from D_Case Where Case_StatusID between 100 and 999 Group By D_Case.Case_CandidateID) Cases on D_Person.Person_ID = Cases.Case_CandidateID ")
                    End Select
                    .Append("WHERE ")
                    If bExactMatch = True Then
                        .Append("D_Person.Person_Lname =  '" & Criteria & "' ")
                    Else
                        .Append("D_Person.Person_Lname like '%' + '" & Criteria & "' + '%' ")
                    End If
                    'If SQLNat.Length > 0 Then
                    '    .Append(SQLNat.ToString)
                    'End If

                    Select Case Nationality
                        Case NationalityMode.NonUK
                            .Append(" and d_person.person_natid <> 71 ")
                        Case NationalityMode.UKOnly
                            .Append(" and d_person.person_natid = 71 ")
                        Case NationalityMode.Specific
                            If NationalityId > 0 Then
                                .Append(" and d_person.person_natid = " & NationalityId)
                            End If
                    End Select

                    .Append(" Order By ID,D_Person.Person_Lname, D_Person.Person_Fname")
                    'DataBinding.BindListView(.ToString, Me.grdSearchResults)
                End With


            Case SearchModes.CompanyName
                With SQL
                    .Append("Select null as ID, '' as Name, null as DOB, '' as Nationality, D_Company.Company_ID as CompanyID, D_Company.Company_Name, D_Company_Parent.Company_Name as ParentCompany, '' as Partner, '' as Email FROM D_Company LEFT OUTER JOIN D_Company D_Company_Parent ON D_Company.Company_ParentID = D_Company_Parent.Company_ID ")

                    Select Case CaseStatus
                        Case CaseStatusMode.ActiveLead
                            .Append(" Inner Join  (Select D_Case.Case_CompanyID from D_Case Where Case_StatusID < 100 Group By D_Case.Case_CompanyID) Cases on D_Company.Company_ID = Cases.Case_CompanyID ")
                        Case CaseStatusMode.AnyOpen
                            .Append(" Inner Join  (Select D_Case.Case_CompanyID from D_Case Where Case_StatusID < 1000 Group By D_Case.Case_CompanyID) Cases on D_Company.Company_ID = Cases.Case_CompanyID ")
                        Case CaseStatusMode.Initiated
                            .Append(" Inner Join  (Select D_Case.Case_CompanyID from D_Case Where Case_StatusID between 100 and 999 Group By D_Case.Case_CompanyID) Cases on D_Company.Company_ID = Cases.Case_CompanyID ")


                    End Select
                    If bExactMatch = True Then
                        .Append("WHERE D_Company.Company_Name = '" & Criteria & "' OR D_Company_Parent.Company_Name = '" & Criteria & "' ")
                    Else
                        .Append("WHERE D_Company.Company_Name like '%" & Criteria & "%' OR D_Company_Parent.Company_Name like '%" & Criteria & "%' ")
                    End If
                    'If SQLNat.Length > 0 Then
                    '    .Append(SQLNat.ToString)
                    'End If
                    'Select Case Nationality
                    '    Case NationalityMode.NonUK
                    '        .Append(" and d_person.person_natid <> 71 ")
                    '    Case NationalityMode.UKOnly
                    '        .Append(" and d_person.person_natid = 71 ")
                    '    Case NationalityMode.Specific
                    '        If NationalityId > 0 Then
                    '            .Append(" and d_person.person_natid = " & NationalityId)
                    '        End If
                    'End Select
                    .Append("Order By ID,isnull(D_Company_Parent.Company_Name,D_Company.Company_Name)")
                    'DataBinding.BindListView(.ToString, Me.grdSearchResults)

                End With

            Case SearchModes.ExRef
                With SQL
                    .Append("Select D_Person.Person_ID as ID, D_Person.Person_Lname + ', ' + D_Person.Person_Fname as Name ,D_Person.person_DoB as DOB, Country_Nationality as Nationality , D_Company.Company_ID as CompanyID, isnull(D_Company.Company_Name,'Private') as Company,D_Company_Parent.Company_Name as ParentCompany, D_Person_Partner.Person_Lname + ', ' + D_Person_Partner.Person_Fname as Partner, D_Case.Case_ExtRef as ExtRef FROM D_Person  INNER JOIN D_Case on D_Person.Person_ID = D_Case.Case_CandidateID LEFT OUTER JOIN D_Company ON D_Person.Person_CompanyID = D_Company.Company_ID LEFT OUTER JOIN D_Company D_Company_Parent ON D_Company.Company_ParentID = D_Company_Parent.Company_ID Left Outer join L_Country on D_Person.Person_NatID = Country_ID LEFT OUTER JOIN (SELECT     D_Dependants.Dependant_ParentID, D_Person.Person_LName, D_Person.Person_FName FROM D_Person INNER JOIN D_Dependants ON D_Person.Person_ID = D_Dependants.Dependant_ChildID WHERE (D_Dependants.Dependant_Spouse = 1)) D_Person_Partner  ON D_Person.Person_ID = D_Person_Partner.Dependant_ParentID ")
                    If bExactMatch = True Then
                        .Append("WHERE D_Case.Case_ExtRef = '" & Criteria & "' ")
                    Else
                        .Append("WHERE D_Case.Case_ExtRef LIKE '%" & Criteria & "%' ")
                    End If
                    'If SQLNat.Length > 0 Then
                    '    .Append(SQLNat.ToString)
                    'End If
                    Select Case Nationality
                        Case NationalityMode.NonUK
                            .Append(" and d_person.person_natid <> 71 ")
                        Case NationalityMode.UKOnly
                            .Append(" and d_person.person_natid = 71 ")
                        Case NationalityMode.Specific
                            If NationalityId > 0 Then
                                .Append(" and d_person.person_natid = " & NationalityId)
                            End If
                    End Select
                    .Append("Order By ID,D_Person.Person_Lname, D_Person.Person_Fname")
                    'DataBinding.BindListView(.ToString, Me.grdSearchResults)
                    'Me.grdSearchResults.Columns(8).Text = "ExtRef"  ' change column name for ExtRef search
                End With

            Case SearchModes.Email
                With SQL
                    .Append("Select D_Person.Person_ID as ID, D_Person.Person_Lname + ', ' + D_Person.Person_Fname as Name ,D_Person.person_DoB as DOB,")
                    .Append("Country_Nationality as Nationality,D_Company.Company_ID as CompanyID, isnull(D_Company.Company_Name,'Private') as Company,D_Company_Parent.Company_Name as ParentCompany, D_Person_Partner.Person_Lname + ', ' + D_Person_Partner.Person_Fname as Partner, EMailAddress.EMailAddress_Address as EMail FROM D_Person INNER JOIN D_EmailAddress EMailAddress on D_Person.Person_ID = EmailAddress.EMailAddress_PersonID LEFT OUTER JOIN D_Company ON D_Person.Person_CompanyID = D_Company.Company_ID LEFT OUTER JOIN D_Company D_Company_Parent ON D_Company.Company_ParentID = D_Company_Parent.Company_ID Left Outer join L_Country on D_Person.Person_NatID = Country_ID LEFT OUTER JOIN (SELECT     D_Dependants.Dependant_ParentID, D_Person.Person_LName, D_Person.Person_FName FROM D_Person INNER JOIN D_Dependants ON D_Person.Person_ID = D_Dependants.Dependant_ChildID WHERE (D_Dependants.Dependant_Spouse = 1)) D_Person_Partner  ON D_Person.Person_ID = D_Person_Partner.Dependant_ParentID ")
                    If bExactMatch = True Then
                        .Append("WHERE emailaddress.emailaddress_address ='" & Criteria & "' ")
                    Else
                        .Append("WHERE emailaddress.emailaddress_address LIKE '%" & Criteria & "%' ")
                    End If
                    'If SQLNat.Length > 0 Then
                    '    .Append(SQLNat.ToString)
                    'End If
                    Select Case Nationality
                        Case NationalityMode.NonUK
                            .Append(" and d_person.person_natid <> 71 ")
                        Case NationalityMode.UKOnly
                            .Append(" and d_person.person_natid = 71 ")
                        Case NationalityMode.Specific
                            If NationalityId > 0 Then
                                .Append(" and d_person.person_natid = " & NationalityId)
                            End If
                    End Select
                    .Append("Order By ID,D_Person.Person_Lname, D_Person.Person_Fname")
                    'DataBinding.BindListView(.ToString, Me.grdSearchResults)
                End With

            Case SearchModes.DOB

                With SQL
                    .Append("Select D_Person.Person_ID as ID, D_Person.Person_Lname + ', ' + D_Person.Person_Fname as Name ,D_Person.person_DoB as DOB, Country_Nationality as Nationality , D_Company.Company_ID as CompanyID, isnull(D_Company.Company_Name,'Private') as Company,D_Company_Parent.Company_Name as ParentCompany, D_Person_Partner.Person_Lname + ', ' + D_Person_Partner.Person_Fname as Partner, EMailAddress.EMailAddress_Address as EMail FROM D_Person  LEFT OUTER JOIN D_Company ON D_Person.Person_CompanyID = D_Company.Company_ID LEFT OUTER JOIN D_Company D_Company_Parent ON D_Company.Company_ParentID = D_Company_Parent.Company_ID Left Outer join L_Country on D_Person.Person_NatID = Country_ID LEFT OUTER JOIN (SELECT     D_Dependants.Dependant_ParentID, D_Person.Person_LName, D_Person.Person_FName FROM D_Person INNER JOIN D_Dependants ON D_Person.Person_ID = D_Dependants.Dependant_ChildID WHERE (D_Dependants.Dependant_Spouse = 1)) D_Person_Partner  ON D_Person.Person_ID = D_Person_Partner.Dependant_ParentID LEFT OUTER JOIN (SELECT D_EMailAddress.EMailAddress_PersonID,")
                    .Append("D_EMailAddress.EMailAddress_Address FROM D_EMailAddress  WHERE (D_EMailAddress.EMailAddress_Type = 1)) EmailAddress  ON D_Person.Person_ID = EMailAddress.EMailAddress_PersonID")
                    'If SQLNat.Length > 0 Then
                    '    .Append(SQLNat.ToString)
                    'End If
                    Select Case CaseStatus
                        Case CaseStatusMode.ActiveLead
                            .Append(" Inner Join (Select D_Case.Case_CandidateID from D_Case Where Case_StatusID < 100 Group By D_Case.Case_CandidateID) Cases on D_Person.Person_ID = Cases.Case_CandidateID ")
                        Case CaseStatusMode.AnyOpen
                            .Append(" Inner Join (Select D_Case.Case_CandidateID from D_Case Where Case_StatusID < 1000 Group By D_Case.Case_CandidateID) Cases on D_Person.Person_ID = Cases.Case_CandidateID ")
                        Case CaseStatusMode.Initiated
                            .Append(" Inner Join (Select D_Case.Case_CandidateID from D_Case Where Case_StatusID between 100 and 999 Group By D_Case.Case_CandidateID) Cases on D_Person.Person_ID = Cases.Case_CandidateID ")
                    End Select

                    Select Case Nationality
                        Case NationalityMode.NonUK
                            .Append(" and d_person.person_natid <> 71 ")
                        Case NationalityMode.UKOnly
                            .Append(" and d_person.person_natid = 71 ")
                        Case NationalityMode.Specific
                            If NationalityId > 0 Then
                                .Append(" and d_person.person_natid = " & NationalityId)
                            End If
                    End Select


                    .Append(" WHERE D_Person.Person_dob = '" & Criteria & "'")
                    .Append("Order By ID,D_Person.Person_Lname,D_Person.Person_Fname")
                    'DataBinding.BindListView(.ToString, Me.grdSearchResults)
                End With
            Case SearchModes.Phone

                With SQL
                    .Append("Select D_Person.Person_ID as ID, D_Person.Person_Lname + ', ' + D_Person.Person_Fname as Name ,D_Person.person_DoB as DOB,")
                    .Append("Country_Nationality as Nationality , D_Company.Company_ID as CompanyID, isnull(D_Company.Company_Name,'Private') as Company,D_Company_Parent.Company_Name as ParentCompany, D_Person_Partner.Person_Lname + ', ' + D_Person_Partner.Person_Fname as Partner, EMailAddress.EMailAddress_Address as EMail FROM D_Person LEFT OUTER JOIN D_Company ON D_Person.Person_CompanyID = D_Company.Company_ID LEFT OUTER JOIN D_Company D_Company_Parent ON D_Company.Company_ParentID = D_Company_Parent.Company_ID Left Outer join L_Country on D_Person.Person_NatID = Country_ID LEFT OUTER JOIN (SELECT     D_Dependants.Dependant_ParentID, D_Person.Person_LName, D_Person.Person_FName FROM D_Person INNER JOIN D_Dependants ON D_Person.Person_ID = D_Dependants.Dependant_ChildID WHERE (D_Dependants.Dependant_Spouse = 1)) D_Person_Partner  ON D_Person.Person_ID = D_Person_Partner.Dependant_ParentID LEFT OUTER JOIN (SELECT D_EMailAddress.EMailAddress_PersonID, D_EMailAddress.EMailAddress_Address FROM D_EMailAddress  WHERE (D_EMailAddress.EMailAddress_Type = 1)) EmailAddress  ON D_Person.Person_ID = EMailAddress.EMailAddress_PersonID ")
                    .Append("inner join D_PhoneNumber on D_Person.Person_ID=D_PhoneNumber.PhoneNumber_PersonId")
                    'If SQLStatus.Length > 0 Then
                    '    .Append(SQLStatus.ToString)
                    'End If
                    Select Case CaseStatus
                        Case CaseStatusMode.ActiveLead
                            .Append(" Inner Join (Select D_Case.Case_CandidateID from D_Case Where Case_StatusID < 100 Group By D_Case.Case_CandidateID) Cases on D_Person.Person_ID = Cases.Case_CandidateID ")
                        Case CaseStatusMode.AnyOpen
                            .Append(" Inner Join (Select D_Case.Case_CandidateID from D_Case Where Case_StatusID < 1000 Group By D_Case.Case_CandidateID) Cases on D_Person.Person_ID = Cases.Case_CandidateID ")
                        Case CaseStatusMode.Initiated
                            .Append(" Inner Join (Select D_Case.Case_CandidateID from D_Case Where Case_StatusID between 100 and 999 Group By D_Case.Case_CandidateID) Cases on D_Person.Person_ID = Cases.Case_CandidateID ")

                    End Select
                    .Append(" WHERE ")
                    If bExactMatch = True Then
                        .Append(" D_PhoneNumber.PhoneNumber_number  =  '" & Criteria & "' ")
                    Else
                        .Append(" D_PhoneNumber.PhoneNumber_number like '%' + '" & Criteria & "' + '%' ")
                    End If
                    'If SQLNat.Length > 0 Then
                    '    .Append(SQLNat.ToString)
                    'End If

                    Select Case Nationality
                        Case NationalityMode.NonUK
                            .Append(" and d_person.person_natid <> 71 ")
                        Case NationalityMode.UKOnly
                            .Append(" and d_person.person_natid = 71 ")
                        Case NationalityMode.Specific
                            If NationalityId > 0 Then
                                .Append(" and d_person.person_natid = " & NationalityId)
                            End If
                    End Select

                    .Append(" Order By ID,D_Person.Person_Fname, D_Person.Person_Lname")
                    'DataBinding.BindListView(.ToString, Me.grdSearchResults)
                End With
        End Select

        ' check search results
        Dim intRecordCount As Integer
        Dim dtbSearchResults As DataTable

        dtbSearchResults = CommonDataFunctions.GetSQLDatatable(SQL.ToString)

        If dtbSearchResults Is Nothing Then
            CommonFuncs.DisplayMessage("The database did not like this criteria. Please retype it and try again.", CommonDialog.CustomDialogIcons.Information, "Search Results")
            Me.grdSearchResults.Items.Clear()
            Exit Sub
        End If

        intRecordCount = dtbSearchResults.Rows.Count

        If intRecordCount = 0 Then
            CommonFuncs.DisplayMessage("No records found matching the search criteria.", CommonDialog.CustomDialogIcons.Information, "Search Results")
            Me.grdSearchResults.ItemsSource = Nothing
            Exit Sub
        End If

        If intRecordCount > 1000 Then
            CommonFuncs.DisplayMessage("Over 1000 matching records found. Please refine your search.", CommonDialog.CustomDialogIcons.Information, "Search Results")
            Me.grdSearchResults.ItemsSource = Nothing
            Exit Sub
        End If

        ' log this search in the D_UserSearch table
        AddUserSearch(Criteria, GlobalCRM.CurrentConID, intRecordCount, SearchMode)

        ' populate results listview
        'DataBinding.BindListView(SQL.ToString, Me.grdSearchResults)

        grdSearchResults.ItemsSource = dtbSearchResults

        'GetColumns(dtbSearchResults)
        'DataBinding.BindListView(dtbSearchResults, Me.grdSearchResults)

        Me.lblRecords.Content = dtbSearchResults.Rows.Count

    End Sub

End Class
